﻿//  --------------------------------
//  Copyright (c) AW Systems, Inc. All rights reserved.
//  This source code is made available under the terms of the Microsoft Public License (Ms-PL)
//  http://www.codeplex.com/SLAM/license
//  ---------------------------------

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Linq;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using Slam.Configuration;
using Slam.Logging;

namespace Slam.Data
{
	internal class SqlDataRepository : IDisposable
	{
		#region Private State

		private DataContext _dataContext;
		private static List<string> _verifiedTableList = new List<string>();
		private static List<string> _updatedTableList = new List<string>();
		private static bool _databaseCreated;

		#endregion

		#region Constructors and Destructors

		public SqlDataRepository(string connectionString)
		{
			_dataContext = new DataContext(connectionString);
		}

		~SqlDataRepository()
		{
			_dataContext.Dispose();
		}

		#endregion

		#region Database Creation

		public void ClearTable(string tableSchema, string tableName)
		{
			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.ClearTable, tableSchema, tableName));
		}

		public void CreateAssociationTable(string tableSchema, string tableName, string firstTableName, string firstColumnName, string firstColumnSqlType, string firstSourceColumnName, string secondTableName, string secondColumnName, string secondColumnSqlType, string secondSourceColumnName)
		{
			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Create Association Table: " + String.Format(Queries.CreateAssociationTable, tableSchema, tableName, firstTableName, firstColumnName, firstColumnSqlType, secondTableName, secondColumnName, secondColumnSqlType, firstSourceColumnName, secondSourceColumnName));

			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.CreateAssociationTable, tableSchema, tableName, firstTableName, firstColumnName, firstColumnSqlType, secondTableName, secondColumnName, secondColumnSqlType, firstSourceColumnName, secondSourceColumnName));
		}

		public void CreateAttachmentTable(string tableSchema, string tableName, string typeTableName, string idColumnName, string idColumnSqlType)
		{
			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Create Attachment Table: " + String.Format(Queries.CreateAttachmentTable, tableSchema, tableName, typeTableName, idColumnName, idColumnSqlType));

			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.CreateAttachmentTable, tableSchema, tableName, typeTableName, idColumnName, idColumnSqlType));
		}

		public void CreateContentTable(string tableSchema, string tableName, IEnumerable<Field> fields)
		{
			string keyFieldName = "";
			string columnList = "";
			string constraints = "";
			foreach (Field field in fields)
			{
				if (field is SqlMappedField)
				{
					SqlMappedField sqlField = (SqlMappedField)field;

					if (String.IsNullOrEmpty(keyFieldName))
					{
						keyFieldName = GetColumnName(sqlField);
					}

					columnList += String.Format("[{0}] [{1} {2}, ", GetColumnName(sqlField), sqlField.SqlType.Contains("(") ? sqlField.SqlType.Replace("(", "](") : sqlField.SqlType + "]", sqlField.Required ? "not null" : "null");
				}

				if (field is SqlMappedAssociationField)
				{
					SqlMappedAssociationField sqlField = (SqlMappedAssociationField)field;
					columnList += String.Format("[{0}] [{1} {2}, ", sqlField.SecondAssociatedTypeIdColumn, sqlField.SecondAssociatedTypeIdSqlType.Contains("(") ? sqlField.SecondAssociatedTypeIdSqlType.Replace("(", "](") : sqlField.SecondAssociatedTypeIdSqlType + "]", sqlField.Required ? "not null" : "null");
					if (sqlField.Required)
					{
						constraints += String.Format(Queries.CreateContentTableConstraint, tableSchema, sqlField.FirstAssociatedTypeTableName, sqlField.SecondAssociatedTypeTableName, sqlField.SecondAssociatedTypeIdColumn, sqlField.SecondAssociatedTypeIdSourceColumn);
					}
				}
			}

			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Create Table: " + String.Format(Queries.CreateContentTable, tableSchema, tableName, columnList, keyFieldName, constraints));

			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.CreateContentTable, tableSchema, tableName, columnList, keyFieldName, constraints));
		}

		public void CreateLocationTables()
		{
			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Create Location Tables: " + Queries.CreateLocationTables);

			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, Queries.CreateLocationTables);
		}

		public void UpdateContentTable(string tableSchema, string tableName, IEnumerable<Field> fields)
		{
			if (!_updatedTableList.Contains(_dataContext.Connection.ConnectionString + tableName))
			{
				string updateQuery = "begin" + Environment.NewLine;
				foreach (Field field in fields)
				{
					if (field is SqlMappedField && !String.IsNullOrEmpty(((SqlMappedField)field).SqlType))
					{
						SqlMappedField sqlField = (SqlMappedField)field;
						updateQuery += String.Format(Queries.UpdateSingleTableColumn, tableSchema, tableName, GetColumnName(sqlField), "[" + GetColumnName(sqlField) + "] [" + (sqlField.SqlType.Contains("(") ? sqlField.SqlType.Replace("(", "](") : sqlField.SqlType + "]") + " " + (sqlField.Required ? "not null" : "null")) + Environment.NewLine + Environment.NewLine;
					}
				}

				updateQuery += "end";

				ILogger logger = ComponentService.GetInstance<ILogger>();
				logger.LogMessage("Information", "Update Table: " + updateQuery);

				_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, updateQuery);

				_updatedTableList.Add(_dataContext.Connection.ConnectionString + tableName);
			}
		}

		public void CreateSchema(string schemaName)
		{
			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.CreateSchema, schemaName));
		}

		public void EnsureDatabaseExists()
		{
			if (!_databaseCreated && !_dataContext.DatabaseExists())
			{
				throw new DataException("Database does not exist.");
				//_dataContext.CreateDatabase();
			}

			_databaseCreated = true;
		}

		public void AddAssociationTableSynchronizationObjects(string tableSchema, string tableName, string firstTypeName, string secondTypeName)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.AddIsSynchedColumn, tableSchema, tableName));
			if (_dataContext.ExecuteQuery<int>(String.Format(Queries.VerifySynchUpdateTriggerExists, tableSchema, tableName)).FirstOrDefault() == 0)
			{
				if (firstTypeName == secondTypeName)
				{
					_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.AddSynchUpdateAssociationTrigger, tableSchema, tableName, firstTypeName, secondTypeName + "2"));
				}
				else
				{
					_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.AddSynchUpdateAssociationTrigger, tableSchema, tableName, firstTypeName, secondTypeName));
				}
			}
		}

		public void AddAttachmentTableSynchronizationObjects(string tableSchema, string tableName, string idColumnName)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.AddIsSynchedColumn, tableSchema, tableName));
			if (_dataContext.ExecuteQuery<int>(SlamItemContext.CommitActivity, String.Format(Queries.VerifySynchUpdateTriggerExists, tableSchema, tableName, idColumnName)).FirstOrDefault() == 0)
			{
				_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.AddSynchUpdateAttachmentTrigger, tableSchema, tableName, idColumnName));
			}
		}

		public void AddTableSynchronizationObjects(string tableSchema, string tableName, string idColumnName)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.AddIsSynchedColumn, tableSchema, tableName));
			if (_dataContext.ExecuteQuery<int>(SlamItemContext.CommitActivity, String.Format(Queries.VerifySynchUpdateTriggerExists, tableSchema, tableName, idColumnName)).FirstOrDefault() == 0)
			{
				_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.AddSynchUpdateTrigger, tableSchema, tableName, idColumnName));
			}
		}

		public int DropTableSynchronizationObjects(string tableSchema, string tableName, bool deleteItems)
		{
			int deletedItems = 0;
			if (deleteItems)
			{
				deletedItems = _dataContext.ExecuteCommand(String.Format(Queries.ClearNonSynchedData, tableSchema, tableName));
			}
			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.RemoveSynchUpdateTrigger, tableSchema, tableName));
			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.RemoveIsSynchedColumn, tableSchema, tableName));
			return deletedItems;
		}

		public bool VerifySchemaExists(string schemaName)
		{
			EnsureDatabaseExists();
			bool exists = false;
			exists = _dataContext.ExecuteQuery<int>(SlamItemContext.CommitActivity, String.Format(Queries.VerifySchemaExists, schemaName)).FirstOrDefault() > 0;
			return exists;
		}

		public bool VerifyTableExists(string tableSchema, string tableName)
		{
			EnsureDatabaseExists();
			bool exists = false;
			if (_verifiedTableList.Contains(_dataContext.Connection.ConnectionString + tableName))
			{
				exists = true;
			}
			else
			{
				exists = _dataContext.ExecuteQuery<int>(SlamItemContext.CommitActivity, String.Format(Queries.VerifyTableExists, tableSchema, tableName)).FirstOrDefault() > 0;
				if (exists)
				{
					_verifiedTableList.Add(_dataContext.Connection.ConnectionString + tableName);
				}
			}
			return exists;
		}

		public void ResetVerifiedTableList()
		{
			_verifiedTableList.Clear();
			_updatedTableList.Clear();
		}

		#endregion

		#region Data Management

		public void DeleteAllAssociations(string tableSchema, string tableName, string columnName, string id)
		{
			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.DeleteAllAssociations, tableSchema, tableName, columnName, id));
		}

		public void DeleteAssociation(string tableSchema, string tableName, string firstColumnName, string firstId, string secondColumnName, string secondId)
		{
			if (firstColumnName == secondColumnName)
			{
				_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.DeleteAssociation, tableSchema, tableName, firstColumnName, firstId, secondColumnName + "2", secondId));
			}
			else
			{
				_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.DeleteAssociation, tableSchema, tableName, firstColumnName, firstId, secondColumnName, secondId));
			}
		}

		public void DeleteAttachments(string tableSchema, string tableName, string idColumnName, string id)
		{
			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Delete Attachments: " + String.Format(Queries.DeleteAllAssociations, tableSchema, tableName, idColumnName, id));
			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.DeleteAllAssociations, tableSchema, tableName, idColumnName, id));
		}

		public void DeleteItem(string tableSchema, string tableName, string id)
		{
			string keyColumnName = "";
			foreach (TableColumn column in RetrieveOrderedColumnNamesForTable(tableSchema, tableName))
			{
				keyColumnName = column.Name;
				break;
			}
			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Delete Item: " + String.Format(Queries.DeleteItem, tableSchema, tableName, id, keyColumnName));
			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.DeleteItem, tableSchema, tableName, id, keyColumnName));
		}

		public void InsertAssociation(string tableSchema, string tableName, string firstColumnName, string firstId, string secondColumnName, string secondId)
		{	
			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.InsertAssociation, tableSchema, tableName, firstColumnName, secondColumnName, firstId, secondId));
		}

		public void InsertAttachment(string tableSchema, string tableName, Hashtable values)
		{
			string columnString = "";
			string valueString = "";
			string columnFormat = "[{0}],";
			foreach (TableColumn column in RetrieveOrderedColumnNamesForTable(tableSchema, tableName))
			{
				string format = "{0},";
				if (!column.Name.Equals("AttachmentID", StringComparison.OrdinalIgnoreCase) && values.ContainsKey(column.Name))
				{
					columnString += String.Format(columnFormat, column.Name);

					string columnValue = values[column.Name].ToString();

					if (column.DataType.Contains("char") || column.DataType.Contains("text") || column.DataType.Equals("uniqueidentifier", StringComparison.OrdinalIgnoreCase))
					{
						format = "'{0}',";
						columnValue = columnValue.Replace("'", "''");
					}
					else if (String.IsNullOrEmpty(columnValue))
					{
						columnValue = "null";
					}

					valueString += String.Format(format, columnValue);
				}
			}
			valueString = valueString.TrimEnd(',');
			columnString = columnString.TrimEnd(',');

			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Insert Attachment Item: " + String.Format(Queries.InsertItem, tableSchema, tableName, columnString, valueString));

			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.InsertItem, tableSchema, tableName, columnString, valueString));
		}

		public void InsertItem(string tableSchema, string tableName, Hashtable values)
		{
			string columnString = "";
			string valueString = "";
			string columnFormat = "[{0}],";
			foreach (TableColumn column in RetrieveOrderedColumnNamesForTable(tableSchema, tableName))
			{
				string format = "{0},";
				if (values.ContainsKey(column.Name))
				{
					columnString += String.Format(columnFormat, column.Name);

					string columnValue = values[column.Name].ToString();

					/* JADE */

					if (column.DataType.Contains("sql_variant"))
					{
						string columnValueType = columnValue.Split(";".ToCharArray())[0];

						columnValue = columnValue.Split(";".ToCharArray())[1].Remove(0, 1);

						switch (columnValueType)
						{
							case "string": column.DataType = "text"; break;
							case "datetime": column.DataType = "date"; break;
							case "boolean": column.DataType = "bit"; columnValue = columnValue.Equals("1") ? "true" : "false"; break;
						}
					}

					/* JADE */

					if (column.DataType.Contains("char") || column.DataType.Contains("text") || column.DataType.Equals("uniqueidentifier", StringComparison.OrdinalIgnoreCase))
					{
						format = "'{0}',";
						columnValue = columnValue.Replace("'", "''");
					}
					else if (String.IsNullOrEmpty(columnValue))
					{
						columnValue = "null";
					}
					else if (column.DataType.Contains("date"))
					{
						format = "convert(datetime, '{0}', 101),";
						DateTime localizedDate = DateTime.Parse(columnValue);
						columnValue = localizedDate.ToString("MM/dd/yyyy HH:mm:ss");
					}
					else if (column.DataType == "bit")
					{
						columnValue = Boolean.Parse(columnValue) ? "1" : "0";
					}

					valueString += String.Format(format, columnValue);
				}
			}
			valueString = valueString.TrimEnd(',');
			columnString = columnString.TrimEnd(',');

			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Insert Item: " + String.Format(Queries.InsertItem, tableSchema, tableName, columnString, valueString));

			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.InsertItem, tableSchema, tableName, columnString, valueString));
		}

		public void UpdateLocation(string siteCollectionId, string siteCollectionUrl,
									 string siteId, string siteName, string siteTitle, string siteUrl,
									 string listId, string listDefaultListViewUrl, string listNewFormUrl, string listEditFormUrl, string listDisplayFormUrl)
		{
			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Update Location: " + String.Format(Queries.UpdateLocation, siteCollectionId, siteCollectionUrl, siteId, siteName, siteTitle, siteUrl, listId, listDefaultListViewUrl, listNewFormUrl, listEditFormUrl, listDisplayFormUrl));

			_dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.UpdateLocation, siteCollectionId, siteCollectionUrl, siteId, siteName, siteTitle, siteUrl, listId, listDefaultListViewUrl, listNewFormUrl, listEditFormUrl, listDisplayFormUrl));
		}

		public bool UpdateItem(string tableSchema, string tableName, string id, Hashtable values)
		{
			string valueString = "";
			string keyColumnName = "";
			foreach (TableColumn column in RetrieveOrderedColumnNamesForTable(tableSchema, tableName))
			{
				if (String.IsNullOrEmpty(keyColumnName))
				{
					keyColumnName = column.Name;
				}

				string format = "[{0}] = {1},";
				if (values.ContainsKey(column.Name))
				{
					string columnValue = values[column.Name].ToString();

					/* JADE */

					if (column.DataType.Contains("sql_variant"))
					{
						string columnValueType = columnValue.Split(";".ToCharArray())[0];

						columnValue = columnValue.Split(";".ToCharArray())[1].Remove(0, 1);

						switch (columnValueType)
						{
							case "string": column.DataType = "text"; break;
							case "datetime": column.DataType = "date"; break;
							case "boolean": column.DataType = "bit"; columnValue = columnValue.Equals("1") ? "true" : "false"; break;
						}
					}

					/* JADE */

					if (column.DataType.Contains("char") || column.DataType.Contains("text") || column.DataType.Equals("uniqueidentifier", StringComparison.OrdinalIgnoreCase))
					{
						format = "[{0}] = '{1}',";
						columnValue = columnValue.Replace("'", "''");
					}
					else if (String.IsNullOrEmpty(columnValue))
					{
						columnValue = "null";
					}
					else if (column.DataType.Contains("date"))
					{
						format = "[{0}] = convert(datetime, '{1}', 101),";
						DateTime localizedDate = DateTime.Parse(columnValue);
						columnValue = localizedDate.ToString("MM/dd/yyyy HH:mm:ss");
					}
					else if (column.DataType == "bit")
					{
						columnValue = Boolean.Parse(columnValue) ? "1" : "0";
					}

					valueString += String.Format(format, column.Name, columnValue);
				}
			}
			valueString = valueString.TrimEnd(',');

			ILogger logger = ComponentService.GetInstance<ILogger>();
			logger.LogMessage("Information", "Update Item: " + String.Format(Queries.UpdateItem, tableSchema, tableName, valueString, id, keyColumnName));

			int rowsUpdated = _dataContext.ExecuteCommand(SlamItemContext.CommitActivity, String.Format(Queries.UpdateItem, tableSchema, tableName, valueString, id, keyColumnName));
			return rowsUpdated > 0;
		}

		private string GetColumnName(SqlMappedField field)
		{
			return String.IsNullOrEmpty(field.Column) ? Regex.Replace(field.Name, "[\\W\\s]", "").Replace("_x0020_", "") : field.Column;
		}

		private IEnumerable<TableColumn> RetrieveOrderedColumnNamesForTable(string tableSchema, string tableName)
		{
			return _dataContext.ExecuteQuery<TableColumn>(SlamItemContext.CommitActivity, String.Format(Queries.RetreiveOrderedColumnsNamesForTable, tableSchema, tableName));
		}

		#endregion

		#region IDisposable Members

		public void Dispose()
		{
			_dataContext.Dispose();
		}

		#endregion
	}

	internal class TableColumn
	{
		public string Name { get; set; }
		public string DataType { get; set; }

		public TableColumn() { }
	}
}
